﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using InterpriseSuiteEcommerceCommon.DTO;
using InterpriseSuiteEcommerceCommon.Extensions;

namespace InterpriseSuiteEcommerceCommon.DataAccess
{
    public class ProductDA
    {
        public static IEnumerable<string> GetProductUnitMeasureAvailability(string customerCode, string itemCode, bool showFromAllWareHouse, bool isAnonymous)
        {
            var unitMeasureCodes = new List<string>();
            using (var con = DB.NewSqlConnection())
            {
                con.Open();
                using (var reader = DB.GetRSFormat(con, "exec eCommerceGetProductUnitMeasureAvailability @CustomerCode = {0}, @ItemCode = {1}, @IncludeAllWarehouses = {2}, @Anon = {3}",
                                                        DB.SQuote(customerCode), 
                                                        DB.SQuote(itemCode), 
                                                        showFromAllWareHouse, 
                                                        isAnonymous))
                {
                    while (reader.Read())
                    {
                        unitMeasureCodes.Add(DB.RSField(reader, "UnitMeasureCode"));
                    }
                }
            }
            return unitMeasureCodes.AsEnumerable();
        }

        public static IEnumerable<Product> GetProductsStockTotal(string languageCode, string filter)
        {
            bool isAllWarehouses = AppLogic.AppConfigBool("ShowInventoryFromAllWarehouses");
            string query = @"SELECT (SELECT SUM(FreeStock) FROM AvailabilityView WHERE ItemCode = IID.ItemCode {1}) AS FreeStock, 
		                                (SELECT SUM(InStock) FROM AvailabilityView WHERE ItemCode = IID.ItemCode {1}) AS InStock, 
		                                (SELECT SUM([Committed]) FROM AvailabilityView WHERE ItemCode = IID.ItemCode {1}) AS [Committed], 
		                                II.ItemCode,
		                                II.ItemName,
		                                IID.ItemDescription, 
                                        II.IsCBN, 
                                        II.CBNItemID
                            FROM InventoryItem II
                            INNER JOIN InventoryItemDescription IID ON IID.ItemCode = II.ItemCode
                            WHERE IID.LanguageCode = {0}".FormatWith(DB.SQuote(languageCode), 
                                                                    (isAllWarehouses) ? String.Empty: " AND WareHouseCode = (SELECT DefaultWarehouse FROM InventoryPreference) ");
            
            if (filter != String.Empty) { query = "SELECT * FROM ({0}) A WHERE {1}".FormatWith(query, filter); }

            var products = new List<Product>();
            using (var con = DB.NewSqlConnection())
            {
                con.Open();
                using (var reader = DB.GetRS(query, con))
                {
                    while (reader.Read())
                    {
                        decimal freeStock = decimal.Zero;
                        bool isCBN = false;
                        int cbnItemId = 0;
                        string itemCode = DB.RSField(reader, "ItemCode");
                        isCBN = DB.RSFieldBool(reader, "IsCBN");
                        cbnItemId = DB.RSFieldInt(reader, "CBNItemID");
                        
                        if (isCBN && !cbnItemId.IsNullOrEmptyTrimmed())
                        {
                            var umInfo = InterpriseHelper.GetItemDefaultUnitMeasure(itemCode);
                            string cbnUMCode = InterpriseHelper.GetCBNUnitMeasureCode(itemCode, umInfo.Code);
                            Interprise.Facade.Base.CBN.CBNTransactionFacade cbnTransactionFacade = new Interprise.Facade.Base.CBN.CBNTransactionFacade();
                            freeStock = cbnTransactionFacade.CheckSupplierAvailableStock(cbnItemId.ToString(), cbnUMCode);
                            freeStock = (freeStock < 0 ? decimal.Zero : freeStock);
                        }
                        else 
                        {
                            freeStock = DB.RSFieldDecimal(reader, "FreeStock");
                        }

                        var item = new Product()
                        {
                            ItemCode = itemCode,
                            ItemName = DB.RSField(reader, "ItemName"),
                            ItemDescription = DB.RSField(reader, "ItemDescription"),
                            StockTotal = new ProductStockTotal()
                            {
                                FreeStock = freeStock,
                                InStock = DB.RSFieldDecimal(reader, "InStock"),
                                Committed = DB.RSFieldDecimal(reader, "Committed")
                            }
                        };
                        products.Add(item);
                    }
                }
            }
            return products.AsEnumerable();
        }

        public static bool UpdateDefaultImageSize(string itemCode, string fileName, string size, string websiteCode)
        { 
            bool retVal = false;
            string query = String.Empty;

            if (size.ToLowerInvariant() == "medium")
            {
                //reset the image defaults
                query = "UPDATE InventoryOverrideImage SET IsDefaultMedium = 0 WHERE ItemCode = {0} AND WebSiteCode = {1}"
                                                        .FormatWith(itemCode.ToDbQuote(), websiteCode.ToDbQuote());
                DB.ExecuteSQL(query);

                query = "UPDATE InventoryOverrideImage SET IsDefaultMedium = 1 WHERE ItemCode = {0} AND [FileName] = {1} AND WebSiteCode = {2}"
                                                        .FormatWith(itemCode.ToDbQuote(), fileName.ToDbQuote(), websiteCode.ToDbQuote());
                DB.ExecuteSQL(query);

                retVal = true;
            }
            else if (size.ToLowerInvariant() == "icon")
            {
                query = "UPDATE InventoryOverrideImage SET IsDefaultIcon = 0 WHERE ItemCode = {0} AND WebSiteCode = {1}"
                                                        .FormatWith(itemCode.ToDbQuote(), websiteCode.ToDbQuote());
                DB.ExecuteSQL(query);

                query = "UPDATE InventoryOverrideImage SET IsDefaultIcon = 1 WHERE ItemCode = {0} AND [FileName] = {1} AND WebSiteCode = {2}"
                                                        .FormatWith(itemCode.ToDbQuote(), fileName.ToDbQuote(), websiteCode.ToDbQuote());
                DB.ExecuteSQL(query);
                retVal = true;
            }

            return retVal;
        }
        
        public static string GetInventoryItemType(string itemCode)
        {
            string itemType = String.Empty;
            using (var con = DB.NewSqlConnection())
            {
                con.Open();
                using (var reader = DB.GetRSFormat(con, String.Format("SELECT ItemType FROM InventoryItem with (NOLOCK) WHERE ItemCode = {0}", itemCode.ToDbQuote())))
                {
                    if (reader.Read()) { itemType = DB.RSField(reader, "ItemType"); }
                }
            }

            return itemType;
        }

        public static int GetInventoryItemCounter(string itemCode)
        {
            int itemCounter = 0;
            using (var con = DB.NewSqlConnection())
            {
                con.Open();
                using (var reader = DB.GetRSFormat(con, String.Format("SELECT Counter FROM InventoryItem with (NOLOCK) WHERE ItemCode = {0}", itemCode.ToDbQuote())))
                {
                    if (reader.Read()) { itemCounter = DB.RSFieldInt(reader, "Counter"); }
                }
            }

            return itemCounter;
        }
    }
}
